#nullable enable

using Application.Core.Entity;

using Dm.util;

using Furion;
using Furion.DataEncryption;
using Furion.Logging;
using Furion.Shapeless;

using SqlSugar;

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Application.Service;

public class DbService
{
	// 将相对路径转换为绝对路径，并确保目录存在
	public static string NormalizeSqliteConnectionString(string? conn, string contentRoot,bool isTest=false)
	{
		string dataSource = isTest? "Test_DB/database.db" : "APP_DB/database.db";
		var original = string.IsNullOrWhiteSpace(conn)
			? $"Data Source={dataSource};Cache=Shared"
			: conn!;
		var parts = original.Split(';', StringSplitOptions.RemoveEmptyEntries).ToList();

		int idx = parts.FindIndex(s =>
			s.TrimStart().StartsWith("Data Source", StringComparison.OrdinalIgnoreCase) ||
			s.TrimStart().StartsWith("DataSource", StringComparison.OrdinalIgnoreCase));



		if (idx >= 0)
		{
			var kv = parts[idx].Split('=', 2);
			if (kv.Length == 2) dataSource = kv[1].Trim();
			var full = Path.IsPathFullyQualified(dataSource)
				? dataSource
				: Path.GetFullPath(Path.Combine(contentRoot, dataSource));
			Directory.CreateDirectory(Path.GetDirectoryName(full)!);
			parts[idx] = $"Data Source={full}";
		}
		else
		{
			var full = Path.GetFullPath(Path.Combine(contentRoot, dataSource));
			Directory.CreateDirectory(Path.GetDirectoryName(full)!);
			parts.Insert(0, $"Data Source={full}");
		}

		return string.Join(';', parts);
	}

	/// <summary>
	/// 创建数据库文件（仅限 SQLite）
	/// </summary>
	/// <param name="db"></param>
	/// <returns>1:创建文件成功,0:文件已存在,-1:创建文件失败</returns>
	public static int CreateDBFile(ISqlSugarClient db)
	{
		var result = 0;

		// 尝试获取连接字符串的多种途径
		//var configuration = app.ApplicationServices.GetService<IConfiguration>();
		string? connString = string.Empty;

		try
		{
			connString = db?.CurrentConnectionConfig?.ConnectionString;
		}
		catch { connString = null; }

		if (string.IsNullOrWhiteSpace(connString))
		{
			try
			{
				connString = db?.Ado?.Connection?.ConnectionString;
			}
			catch { connString = null; }
		}

		if (string.IsNullOrWhiteSpace(connString))
		{
			// 尝试常见位置回退
			connString = Furion.App.Configuration["ConnectionStrings:Sqlite"];
			// 也可根据项目自定义节点读取
		}

		// 提取 SQLite 文件路径的函数
		string ExtractSqliteFilePath(string cs)
		{
			if (string.IsNullOrWhiteSpace(cs)) return string.Empty;

			// 查找 Data Source 或 Filename 等键，键值对形式 key=value;key2=value2
			// 使用正则忽略大小写并提取第一个匹配值
			var patterns = new[] { "data source", "datasource", "filename", "file name", "file" };
			foreach (var key in patterns)
			{
				var regex = new Regex($@"{Regex.Escape(key)}\s*=\s*([^;]+)", RegexOptions.IgnoreCase);
				var m = regex.Match(cs);
				if (m.Success)
				{
					var val = m.Groups[1].Value.Trim().Trim('"').Trim();
					return val;
				}
			}

			// 如果连接字符串本身可能直接是一个文件名（不常见），尝试检测 .db 或 .sqlite 扩展
			if (cs.IndexOf(".db", StringComparison.OrdinalIgnoreCase) >= 0
				|| cs.IndexOf(".sqlite", StringComparison.OrdinalIgnoreCase) >= 0)
			{
				// 取到第一个看起来像路径的片段
				var seg = cs.Split(';', StringSplitOptions.RemoveEmptyEntries)
							.Select(s => s.Trim())
							.FirstOrDefault(s => s.IndexOf(".db", StringComparison.OrdinalIgnoreCase) >= 0
												|| s.IndexOf(".sqlite", StringComparison.OrdinalIgnoreCase) >= 0);
				if (seg != null)
				{
					// 如果 seg 中包含 '='，取 '=' 右侧
					var idx = seg.IndexOf('=');
					if (idx >= 0) return seg.Substring(idx + 1).Trim().Trim('"');
					return seg;
				}
			}

			return string.Empty;
		}

		var filePathRaw = ExtractSqliteFilePath(connString ?? string.Empty);

		// 处理 |DataDirectory| 占位符
		if (!string.IsNullOrWhiteSpace(filePathRaw))
		{
			var dataDir = AppDomain.CurrentDomain.GetData("DataDirectory") as string
							?? Furion.App.HostEnvironment.ContentRootPath;

			if (filePathRaw.Contains("|DataDirectory|", StringComparison.OrdinalIgnoreCase))
			{
				filePathRaw = filePathRaw.Replace("|DataDirectory|", dataDir, StringComparison.OrdinalIgnoreCase);
			}

			// 去掉可能的 URI 前缀 "Data Source=file:..." (file:) 的情况
			if (filePathRaw.StartsWith("file:", StringComparison.OrdinalIgnoreCase))
			{
				// file:relative/path.db 或 file:/absolute/path.db
				var withoutPrefix = filePathRaw.Substring(5).TrimStart('/');
				filePathRaw = withoutPrefix;
			}

			// 如果是相对路径，则基于 ContentRootPath 解析为绝对路径
			string absolutePath = filePathRaw;
			if (!Path.IsPathRooted(absolutePath))
			{
				absolutePath = Path.Combine(Furion.App.HostEnvironment.ContentRootPath, absolutePath);
			}

			// 规范化路径
			absolutePath = Path.GetFullPath(absolutePath);

			try
			{
				var dir = Path.GetDirectoryName(absolutePath);
				if (!string.IsNullOrWhiteSpace(dir) && !Directory.Exists(dir))
				{
					Directory.CreateDirectory(dir);
					Log.Information("SqlSugar：已创建 SQLite 数据库文件目录：{Dir}", dir);
				}

				if (File.Exists(absolutePath))
				{
					Log.Information("SqlSugar：SQLite 数据库文件已存在：{Path}", absolutePath);
				}
				else
				{
					// 如果文件不存在，尝试使用 SqlSugar 的 CreateDatabase 来创建，并记录结果
					var created = db?.DbMaintenance.CreateDatabase()??false;
					if (created)
					{
						result = 1;
						Log.Information("SqlSugar：已创建 SQLite 数据库文件（由 SqlSugar 创建）：{Path}", absolutePath);
					}
					else
					{
						// CreateDatabase 可能返回 false 即使文件被创建（不同版本行为），再做一次文件存在检查
						if (File.Exists(absolutePath))
						{
							Log.Information("SqlSugar：SQLite 数据库文件已创建（检测到文件）：{Path}", absolutePath);
						}
						else
						{
							Log.Warning("SqlSugar：未能确认 SQLite 数据库文件是否已创建：{Path}", absolutePath);
							result = -1;
						}
					}
				}
			}
			catch (Exception exPath)
			{
				Log.Error($"SqlSugar：尝试确保 SQLite 文件路径时发生错误，路径：{filePathRaw}", exception: exPath);
				// 回退到直接调用 CreateDatabase，以保证尽可能创建数据库
				try
				{
					var fallback = db?.DbMaintenance.CreateDatabase()??false;
					result = 1;
					Log.Information("SqlSugar：回退 CreateDatabase 调用结果：{Result}", fallback);
				}
				catch (Exception exFallback)
				{
					Log.Error("SqlSugar：回退 CreateDatabase 调用失败。", exception: exFallback);
					result = -1;
				}
			}
		}
		else
		{
			// 未能解析出文件路径，仍然尝试创建数据库以保证兼容性
			var isCreated = false;
			try
			{
				isCreated = db?.DbMaintenance.CreateDatabase()??false;
				result = 1;
				Log.Information("SqlSugar：未解析出具体文件路径，已调用 CreateDatabase，结果：{Result}", isCreated);
			}
			catch (Exception exCreate)
			{
				Log.Error("SqlSugar：调用 CreateDatabase 以创建数据库时失败。", exception: exCreate);
				result = -1;
			}
		}
		return result;
	}

	/// <summary>
	/// 种子数据
	/// </summary>
	/// <param name="db"></param>
	/// <returns></returns>
	public static bool SeedDataCommit(ISqlSugarClient db)
	{
		var result = false;
		// 获取 ILogger<Startup> 实例
		//新增User、Permission、UserPermission种子数据
		try
		{
			// 使用事务保证原子性
			db.Ado.BeginTran();

			// 1. 确保 Admin 用户存在
			var adminUser = db.Queryable<User>().First(u => u.UserName == "Admin");
			int adminId;
			if (adminUser == null)
			{
				var pwdHash = MD5Encryption.Encrypt(MD5Encryption.Encrypt($"{App.GetConfig<string>("SecretKey")}123456"),true,false);
				var newUser = new User
				{
					UserName = "Admin",
					DisplayName = "超级管理员",
					PasswordHash = pwdHash,
					IsDisabled = false,
					CreatedAt = DateTime.UtcNow
				};
				
				var insertedId = db.Insertable(newUser).ExecuteReturnIdentity();
				adminId = Convert.ToInt32(insertedId);
				Log.Information("SqlSugar：已创建 Admin 用户，Id={Id}", adminId);
			}
			else
			{
				adminId = adminUser.Id;
				Log.Information("SqlSugar：Admin用户 已存在，Id={Id}", adminId);
			}

			var onlyReadUser = db.Queryable<User>().First(u => u.UserName == "User");
			int onlyReadUserId;
			if (onlyReadUser == null)
			{
				var pwdHash = MD5Encryption.Encrypt(MD5Encryption.Encrypt($"{App.GetConfig<string>("SecretKey")}123456"), true, false);
				var newUser = new User
				{
					UserName = "User",
					DisplayName = "只读用户",
					PasswordHash = pwdHash,
					IsDisabled = false,
					CreatedAt = DateTime.UtcNow
				};
				var insertedId = db.Insertable(newUser).ExecuteReturnIdentity();
				onlyReadUserId = Convert.ToInt32(insertedId);
				Log.Information("SqlSugar：已创建 只读用户User，Id={Id}", adminId);
			}
			else
			{
				onlyReadUserId = onlyReadUser.Id;
				Log.Information("SqlSugar：只读用户User 已存在，Id={Id}", onlyReadUserId);
			}

			// 2. 增加当前项目中四个基本页面的权限（首页的可读/可读写，用户的可读/增改删,权限的可读/增改删,日志可读）

			//var routes = Clay.Parse(new[]
			//	{
			//		new KeyValuePair<string, string>("main",  "首页"),
			//		new KeyValuePair<string, string>("main.read", "首页[只读]"),
			//		new KeyValuePair<string, string>("user",  "用户"),
			//		new KeyValuePair<string, string>("user.read", "用户[只读]"),
			//		new KeyValuePair<string, string>("user.create", "创建用户"),
			//		new KeyValuePair<string, string>("user.modfiy", "编辑用户"),
			//		new KeyValuePair<string, string>("user.delete", "删除用户"),
			//		new KeyValuePair<string, string>("permission",  "权限"),
			//		new KeyValuePair<string, string>("permission.read", "权限[只读]"),
			//		new KeyValuePair<string, string>("permission.create", "创建权限"),
			//		new KeyValuePair<string, string>("permission.modfiy", "编辑权限"),
			//		new KeyValuePair<string, string>("permission.delete", "删除权限"),
			//		new KeyValuePair<string, string>("log",  "日志")
			//	}.ToDictionary());

			var routes = new List<Clay>();
			routes.add<Clay>(Clay.Parse("""{"code":"main","name":"首页"}"""));
			//routes.add<Clay>(Clay.Parse("""{"code":"main.read","name":"首页[只读]"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"user","name":"用户"}"""));
			//routes.add<Clay>(Clay.Parse("""{"code":"user.read","name":"用户[只读]"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"user.create","name":"创建用户"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"user.modify","name":"编辑用户"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"user.delete","name":"删除用户"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"permission","name":"权限"}"""));
			//routes.add<Clay>(Clay.Parse("""{"code":"permission.read","name":"权限[只读]"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"permission.create","name":"创建权限"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"permission.modify","name":"编辑权限"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"permission.delete","name":"删除权限"}"""));
			routes.add<Clay>(Clay.Parse("""{"code":"log","name":"日志"}"""));

		
			var permissionIds = new List<int>();
			var onlyReadPermissionIds = new List<int>();
			foreach (dynamic route in routes)
			{
				string _code = route.code;
				string _name = route.name;
				var perm = db.Queryable<Permission>().First(p => p.Code == _code);
				if (perm == null)
				{
					var newPerm = new Permission
					{
						Code = _code,
						Name = _name,
						Description = _name,
						OwnerUserId = adminId
					};
					var permInserted = db.Insertable(newPerm).ExecuteReturnIdentity();
					var permId = Convert.ToInt32(permInserted);
					permissionIds.Add(permId);
					if (!_code.Contains("."))
						onlyReadPermissionIds.Add(permId);
					Log.Information("SqlSugar：已创建权限 {Route}，Id={Id}", _name, permId);
				}
				else
				{
					permissionIds.Add(perm.Id);
					Log.Information("SqlSugar：权限已存在 {Route}，Id={Id}", _name, perm.Id);
				}
			}

			// 3. 将所有权限与 Admin 用户关联（避免重复）
			foreach (var pid in permissionIds)
			{
				var exists = db.Queryable<UserPermission>().Any(up => up.UserId == adminId && up.PermissionId == pid);
				if (!exists)
				{
					db.Insertable(new UserPermission { UserId = adminId, PermissionId = pid }).ExecuteCommand();
					Log.Information("SqlSugar：已为 Admin用户 添加权限 Id={Id}", pid);
				}
			}
			foreach (var pid in onlyReadPermissionIds)
			{
				var existOnlyReads = db.Queryable<UserPermission>().Any(up => up.UserId == onlyReadUserId && up.PermissionId == pid);
				if (!existOnlyReads)
				{
					db.Insertable(new UserPermission { UserId = onlyReadUserId, PermissionId = pid, OnlyRead=true }).ExecuteCommand();
					Log.Information("SqlSugar：已为 只读用户 添加权限 Id={Id}", pid);
				}
			}

			db.Ado.CommitTran();
			result = true;
		}
		catch (Exception exSeed)
		{
			try { db.Ado.RollbackTran(); } catch { }
			Log.Error("SqlSugar：插入种子数据时出错。", exception: exSeed);
		}
		return result;
	}
}
